{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>BU2012</th>\n",
       "      <th>BU2006</th>\n",
       "      <th>价差</th>\n",
       "      <th>BU2012NP</th>\n",
       "      <th>BU2006NP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20200217</td>\n",
       "      <td>3004.0</td>\n",
       "      <td>2992.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20200218</td>\n",
       "      <td>2992.0</td>\n",
       "      <td>2974.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7478.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20200219</td>\n",
       "      <td>3112.0</td>\n",
       "      <td>3066.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20200220</td>\n",
       "      <td>3146.0</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2328.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20200221</td>\n",
       "      <td>3118.0</td>\n",
       "      <td>3088.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1274.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20200224</td>\n",
       "      <td>3084.0</td>\n",
       "      <td>3044.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>805.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>20200225</td>\n",
       "      <td>3136.0</td>\n",
       "      <td>3088.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2091.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20200226</td>\n",
       "      <td>3038.0</td>\n",
       "      <td>2958.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20200227</td>\n",
       "      <td>2948.0</td>\n",
       "      <td>2852.0</td>\n",
       "      <td>96.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20200228</td>\n",
       "      <td>2848.0</td>\n",
       "      <td>2736.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9297.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>20200302</td>\n",
       "      <td>2964.0</td>\n",
       "      <td>2860.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1579.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>20200303</td>\n",
       "      <td>2998.0</td>\n",
       "      <td>2906.0</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8339.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>20200304</td>\n",
       "      <td>3006.0</td>\n",
       "      <td>2906.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9232.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>20200305</td>\n",
       "      <td>3014.0</td>\n",
       "      <td>2902.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6359.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>20200306</td>\n",
       "      <td>2944.0</td>\n",
       "      <td>2822.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9861.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>20200309</td>\n",
       "      <td>2740.0</td>\n",
       "      <td>2628.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10549.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20200310</td>\n",
       "      <td>2490.0</td>\n",
       "      <td>2366.0</td>\n",
       "      <td>124.0</td>\n",
       "      <td>4037.0</td>\n",
       "      <td>-1023.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>20200311</td>\n",
       "      <td>2450.0</td>\n",
       "      <td>2264.0</td>\n",
       "      <td>186.0</td>\n",
       "      <td>6122.0</td>\n",
       "      <td>-2341.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20200312</td>\n",
       "      <td>2346.0</td>\n",
       "      <td>2180.0</td>\n",
       "      <td>166.0</td>\n",
       "      <td>6421.0</td>\n",
       "      <td>-5000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20200313</td>\n",
       "      <td>2336.0</td>\n",
       "      <td>2118.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>4318.0</td>\n",
       "      <td>-511.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20200316</td>\n",
       "      <td>2220.0</td>\n",
       "      <td>2050.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>2883.0</td>\n",
       "      <td>8061.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20200317</td>\n",
       "      <td>2208.0</td>\n",
       "      <td>2052.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>3146.0</td>\n",
       "      <td>14296.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>20200318</td>\n",
       "      <td>2072.0</td>\n",
       "      <td>1920.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>3745.0</td>\n",
       "      <td>17397.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>20200319</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>1820.0</td>\n",
       "      <td>168.0</td>\n",
       "      <td>3564.0</td>\n",
       "      <td>10926.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>20200320</td>\n",
       "      <td>2128.0</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>188.0</td>\n",
       "      <td>3896.0</td>\n",
       "      <td>8404.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>20200323</td>\n",
       "      <td>2050.0</td>\n",
       "      <td>1888.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>4872.0</td>\n",
       "      <td>37111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>20200324</td>\n",
       "      <td>2148.0</td>\n",
       "      <td>1986.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>4247.0</td>\n",
       "      <td>23445.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>20200325</td>\n",
       "      <td>2166.0</td>\n",
       "      <td>1996.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>464.0</td>\n",
       "      <td>21652.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20200326</td>\n",
       "      <td>2070.0</td>\n",
       "      <td>1880.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>1471.0</td>\n",
       "      <td>16083.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>20200327</td>\n",
       "      <td>2082.0</td>\n",
       "      <td>1872.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>-6858.0</td>\n",
       "      <td>23449.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>20200330</td>\n",
       "      <td>2024.0</td>\n",
       "      <td>1776.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>-5591.0</td>\n",
       "      <td>20383.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>20200331</td>\n",
       "      <td>2088.0</td>\n",
       "      <td>1860.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>-1615.0</td>\n",
       "      <td>23453.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>20200401</td>\n",
       "      <td>2096.0</td>\n",
       "      <td>1878.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>-1361.0</td>\n",
       "      <td>17854.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>20200402</td>\n",
       "      <td>2240.0</td>\n",
       "      <td>2028.0</td>\n",
       "      <td>212.0</td>\n",
       "      <td>-283.0</td>\n",
       "      <td>8557.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>20200403</td>\n",
       "      <td>2284.0</td>\n",
       "      <td>2038.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>-1880.0</td>\n",
       "      <td>12751.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>20200407</td>\n",
       "      <td>2378.0</td>\n",
       "      <td>2150.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>13473.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>20200408</td>\n",
       "      <td>2350.0</td>\n",
       "      <td>2128.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>-135.0</td>\n",
       "      <td>11063.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>20200409</td>\n",
       "      <td>2404.0</td>\n",
       "      <td>2178.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>8565.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>20200410</td>\n",
       "      <td>2382.0</td>\n",
       "      <td>2160.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>-538.0</td>\n",
       "      <td>10204.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>20200413</td>\n",
       "      <td>2394.0</td>\n",
       "      <td>2156.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>1148.0</td>\n",
       "      <td>5559.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>20200414</td>\n",
       "      <td>2394.0</td>\n",
       "      <td>2164.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>593.0</td>\n",
       "      <td>-2152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>20200415</td>\n",
       "      <td>2414.0</td>\n",
       "      <td>2188.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>4592.0</td>\n",
       "      <td>17928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>20200416</td>\n",
       "      <td>2392.0</td>\n",
       "      <td>2154.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>8660.0</td>\n",
       "      <td>16002.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>20200417</td>\n",
       "      <td>2396.0</td>\n",
       "      <td>2144.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>7892.0</td>\n",
       "      <td>-10199.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>20200420</td>\n",
       "      <td>2386.0</td>\n",
       "      <td>2122.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>13412.0</td>\n",
       "      <td>-5078.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>20200421</td>\n",
       "      <td>2218.0</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>278.0</td>\n",
       "      <td>11713.0</td>\n",
       "      <td>-200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>20200422</td>\n",
       "      <td>2118.0</td>\n",
       "      <td>1834.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>16576.0</td>\n",
       "      <td>10158.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>20200423</td>\n",
       "      <td>2312.0</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>292.0</td>\n",
       "      <td>13962.0</td>\n",
       "      <td>20535.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>20200424</td>\n",
       "      <td>2350.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>334.0</td>\n",
       "      <td>12048.0</td>\n",
       "      <td>2571.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>20200427</td>\n",
       "      <td>2292.0</td>\n",
       "      <td>2022.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>8537.0</td>\n",
       "      <td>7191.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>20200428</td>\n",
       "      <td>2248.0</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>-495.0</td>\n",
       "      <td>2296.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>20200429</td>\n",
       "      <td>2326.0</td>\n",
       "      <td>2046.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>865.0</td>\n",
       "      <td>868.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>20200430</td>\n",
       "      <td>2348.0</td>\n",
       "      <td>2052.0</td>\n",
       "      <td>296.0</td>\n",
       "      <td>-302.0</td>\n",
       "      <td>9328.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>20200506</td>\n",
       "      <td>2390.0</td>\n",
       "      <td>2128.0</td>\n",
       "      <td>262.0</td>\n",
       "      <td>-5133.0</td>\n",
       "      <td>11015.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>20200507</td>\n",
       "      <td>2358.0</td>\n",
       "      <td>2112.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>-6814.0</td>\n",
       "      <td>11560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>20200508</td>\n",
       "      <td>2378.0</td>\n",
       "      <td>2110.0</td>\n",
       "      <td>268.0</td>\n",
       "      <td>-4661.0</td>\n",
       "      <td>10703.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  BU2012  BU2006     价差  BU2012NP  BU2006NP\n",
       "0   20200217  3004.0  2992.0   12.0       0.0    7455.0\n",
       "1   20200218  2992.0  2974.0   18.0       0.0    7478.0\n",
       "2   20200219  3112.0  3066.0   46.0       0.0    5145.0\n",
       "3   20200220  3146.0  3100.0   46.0       0.0    2328.0\n",
       "4   20200221  3118.0  3088.0   30.0       0.0    1274.0\n",
       "5   20200224  3084.0  3044.0   40.0       0.0     805.0\n",
       "6   20200225  3136.0  3088.0   48.0       0.0    2091.0\n",
       "7   20200226  3038.0  2958.0   80.0       0.0     160.0\n",
       "8   20200227  2948.0  2852.0   96.0       0.0    8278.0\n",
       "9   20200228  2848.0  2736.0  112.0       0.0    9297.0\n",
       "10  20200302  2964.0  2860.0  104.0       0.0    1579.0\n",
       "11  20200303  2998.0  2906.0   92.0       0.0    8339.0\n",
       "12  20200304  3006.0  2906.0  100.0       0.0    9232.0\n",
       "13  20200305  3014.0  2902.0  112.0       0.0    6359.0\n",
       "14  20200306  2944.0  2822.0  122.0       0.0    9861.0\n",
       "15  20200309  2740.0  2628.0  112.0       0.0   10549.0\n",
       "16  20200310  2490.0  2366.0  124.0    4037.0   -1023.0\n",
       "17  20200311  2450.0  2264.0  186.0    6122.0   -2341.0\n",
       "18  20200312  2346.0  2180.0  166.0    6421.0   -5000.0\n",
       "19  20200313  2336.0  2118.0  218.0    4318.0    -511.0\n",
       "20  20200316  2220.0  2050.0  170.0    2883.0    8061.0\n",
       "21  20200317  2208.0  2052.0  156.0    3146.0   14296.0\n",
       "22  20200318  2072.0  1920.0  152.0    3745.0   17397.0\n",
       "23  20200319  1988.0  1820.0  168.0    3564.0   10926.0\n",
       "24  20200320  2128.0  1940.0  188.0    3896.0    8404.0\n",
       "25  20200323  2050.0  1888.0  162.0    4872.0   37111.0\n",
       "26  20200324  2148.0  1986.0  162.0    4247.0   23445.0\n",
       "27  20200325  2166.0  1996.0  170.0     464.0   21652.0\n",
       "28  20200326  2070.0  1880.0  190.0    1471.0   16083.0\n",
       "29  20200327  2082.0  1872.0  210.0   -6858.0   23449.0\n",
       "30  20200330  2024.0  1776.0  248.0   -5591.0   20383.0\n",
       "31  20200331  2088.0  1860.0  228.0   -1615.0   23453.0\n",
       "32  20200401  2096.0  1878.0  218.0   -1361.0   17854.0\n",
       "33  20200402  2240.0  2028.0  212.0    -283.0    8557.0\n",
       "34  20200403  2284.0  2038.0  246.0   -1880.0   12751.0\n",
       "35  20200407  2378.0  2150.0  228.0     100.0   13473.0\n",
       "36  20200408  2350.0  2128.0  222.0    -135.0   11063.0\n",
       "37  20200409  2404.0  2178.0  226.0      97.0    8565.0\n",
       "38  20200410  2382.0  2160.0  222.0    -538.0   10204.0\n",
       "39  20200413  2394.0  2156.0  238.0    1148.0    5559.0\n",
       "40  20200414  2394.0  2164.0  230.0     593.0   -2152.0\n",
       "41  20200415  2414.0  2188.0  226.0    4592.0   17928.0\n",
       "42  20200416  2392.0  2154.0  238.0    8660.0   16002.0\n",
       "43  20200417  2396.0  2144.0  252.0    7892.0  -10199.0\n",
       "44  20200420  2386.0  2122.0  264.0   13412.0   -5078.0\n",
       "45  20200421  2218.0  1940.0  278.0   11713.0    -200.0\n",
       "46  20200422  2118.0  1834.0  284.0   16576.0   10158.0\n",
       "47  20200423  2312.0  2020.0  292.0   13962.0   20535.0\n",
       "48  20200424  2350.0  2016.0  334.0   12048.0    2571.0\n",
       "49  20200427  2292.0  2022.0  270.0    8537.0    7191.0\n",
       "50  20200428  2248.0  1988.0  260.0    -495.0    2296.0\n",
       "51  20200429  2326.0  2046.0  280.0     865.0     868.0\n",
       "52  20200430  2348.0  2052.0  296.0    -302.0    9328.0\n",
       "53  20200506  2390.0  2128.0  262.0   -5133.0   11015.0\n",
       "54  20200507  2358.0  2112.0  246.0   -6814.0   11560.0\n",
       "55  20200508  2378.0  2110.0  268.0   -4661.0   10703.0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# encoding: utf-8\n",
    "import pandas as pd\n",
    "import pymongo\n",
    "from pandas import DataFrame\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号\n",
    "\n",
    "client = pymongo.MongoClient('localhost', 27017)\n",
    "futures = client.futures2\n",
    "market = futures.market\n",
    "position = futures.position\n",
    "\n",
    "start = '20200215'\n",
    "end ='20200508'\n",
    "symbol1 = 'BU2012'\n",
    "symbol2 = 'BU2006'\n",
    "BrokerID = '永安期货'\n",
    "market1 = DataFrame(list(market.find({'date': {'$gte': start}, 'symbol':symbol1}))).drop_duplicates(['date','variety','symbol'], 'last')\n",
    "market2 = DataFrame(list(market.find({'date': {'$gte': start}, 'symbol': symbol2}))).drop_duplicates(['date','variety','symbol'], 'last')\n",
    "position1 = DataFrame(list(position.find({'date': {'$gte': start},'symbol': symbol1}))).dropna().drop_duplicates(['date','variety','symbol','long_party_name'], 'last')\n",
    "position2 = DataFrame(list(position.find({'date': {'$gte': start},'symbol': symbol2,}))).dropna().drop_duplicates(['date','variety','symbol','long_party_name'], 'last')\n",
    "# print(position2)\n",
    "# 主力收盘\n",
    "market1[symbol1] = market1['close']\n",
    "# 次主力收盘\n",
    "market2[symbol2] = market2['close']\n",
    "\n",
    "#######\n",
    "#position1\n",
    "data3=position1[position1['long_party_name'] == BrokerID]\n",
    "data3=data3[['date','symbol','long_party_name','long_openIntr']]\n",
    "data3=data3.groupby(['date','symbol','long_party_name'])[['long_openIntr']].sum()\n",
    "data4=position1[position1['short_party_name'] == BrokerID]\n",
    "data4=data4[['date','symbol','short_party_name','short_openIntr']]\n",
    "data4=data4.groupby(['date','symbol','short_party_name'])[['short_openIntr']].sum()\n",
    "# #并集\n",
    "data5=pd.merge(data3,data4, on=['date','symbol'],how='outer')\n",
    "data5['会员简称']=data5.apply(lambda x: BrokerID,axis=1)\n",
    "#nan缺失值填充fillna()为0\n",
    "data5=data5.fillna(0)\n",
    "#选择需要显示的字段\n",
    "data5=data5[['会员简称','long_openIntr','short_openIntr']]\n",
    "position1=data5.reset_index(['symbol','date'])\n",
    "# print(position1)\n",
    "\n",
    "#########\n",
    "# position2\n",
    "data3=position2[position2['long_party_name'] == BrokerID]\n",
    "data3=data3[['date','symbol','long_party_name','long_openIntr']]\n",
    "data3=data3.groupby(['date','symbol','long_party_name'])[['long_openIntr']].sum()\n",
    "data4=position2[position2['short_party_name'] == BrokerID]\n",
    "data4=data4[['date','symbol','short_party_name','short_openIntr']]\n",
    "data4=data4.groupby(['date','symbol','short_party_name'])[['short_openIntr']].sum()\n",
    "# #并集\n",
    "data5=pd.merge(data3,data4, on=['date','symbol'],how='outer')\n",
    "data5['会员简称']=data5.apply(lambda x: BrokerID,axis=1)\n",
    "#nan缺失值填充fillna()为0\n",
    "data5=data5.fillna(0)\n",
    "#选择需要显示的字段\n",
    "data5=data5[['会员简称','long_openIntr','short_openIntr']]\n",
    "position2=data5.reset_index(['symbol','date'])\n",
    "# print(position2)\n",
    "#########\n",
    "\n",
    "\n",
    "# #两表合并\n",
    "merge = pd.merge(market1,market2, on=['date'], how='left').sort_values(['date'],ascending=True)\n",
    "merge = merge[['date',symbol1,symbol2]]\n",
    "merge['价差'] = merge.apply(lambda x: x[symbol1] - x[symbol2], axis=1)\n",
    "净持仓1=symbol1+'NP'\n",
    "净持仓2=symbol2+'NP'\n",
    "\n",
    "position1[净持仓1]=position1.apply(lambda x:x['long_openIntr']-x['short_openIntr'],axis=1)\n",
    "position2[净持仓2]=position2.apply(lambda x:x['long_openIntr']-x['short_openIntr'],axis=1)\n",
    "\n",
    "merge1 = pd.merge(position1,position2, on=['date'], how='outer').fillna(0)\n",
    "merge1=merge1[['date',净持仓1,净持仓2]]\n",
    "merge2=pd.merge(merge,merge1,on=['date'],how='outer')\n",
    "merge2\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<img src=\"\" width=\"640\">"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%matplotlib notebook\n",
    "#二行即可搞定画图中文乱码\n",
    "plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus']=False #用来正常显示负号\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus']=False #用来正常显示负号\n",
    "# 画图\n",
    "dates=merge2['date']\n",
    "plt.plot(dates,merge2['价差'],label='价差',color='r')\n",
    "plt.ylabel('价差')\n",
    "plt.tick_params(axis='x',rotation=45) #日期斜体\n",
    "plt.twinx()\n",
    "plt.plot(dates,merge2[净持仓1],label=净持仓1,color='b')\n",
    "plt.plot(dates,merge2[净持仓2],label=净持仓2,color='y')\n",
    "plt.legend()\n",
    "plt.ylabel('净持仓')\n",
    "plt.grid(linestyle=\"--\", alpha=0.3)\n",
    "plt.title(BrokerID+'  '+merge2['date'].iloc[0]+\" \"+symbol1+\" \"+symbol2+'  '+merge2['date'].iloc[-1])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
